package com.dp.appi.dao.impl;

import com.dp.appi.dao.ExpCodesetDao;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xiaoji on 2016/5/13.
 * 典设材料
 */
@Component
public class ExpCodesetDaoImpl extends BaseDaoImpl implements ExpCodesetDao {
    /**
     * 保存踏勘典设方案
     *
     * @param codeset_id 典设方案id
     */
    @Override
    public void saveExpCodeset(Long codeset_id, Long exploration_id) {

        String delSql = "delete from p_exploration_material where exploration_id = ?";
        getJdbcTemplate().update(delSql, new Object[]{exploration_id});

        String sql = "insert into p_exploration_material (material_number, erp_id, erp_code, exploration_id, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id,sort) " +
                "select material_number, erp_id, erp_code, ?, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id,sort from c_codeset_material where codeset_id=?";
        getJdbcTemplate().update(sql, new Object[]{exploration_id, codeset_id});
    }

    /**
     * 重新计算汇总
     *
     * @param exploration_id 踏勘id
     */
    @Override
    public void updateExpCodeset(Long exploration_id) {
        //获取典设材料
        List<Map<String, Object>> list1 = getPexplorationMaterial(exploration_id);
        //获取典设模块材料
        List<Map<String, Object>> list2 = getCmoduleMaterial(exploration_id);
        //排除2个List中重复的相同数量相加
        List<Map<String, Object>> listAll = list1CopyList2(list1, list2);
        //获取材料汇总列表
        List<Map<String, Object>> listSum = getPexplorationMaterialSum(exploration_id);
        //初始化list1中数据是插入还是更新
        List<Map<String, Object>> insertList = new ArrayList<>();
        List<Map<String, Object>> updateList = new ArrayList<>();
        List<Map<String, Object>> deleteList = new ArrayList<>();
        initObjects(listAll, listSum, insertList, updateList, deleteList);
        //批量插入
        batchInsertMaterial(insertList, exploration_id);
        //批量更新
        batchUpdateMaterial(updateList);
        //批量删除
        batchDeleteMaterial(deleteList);
    }

    /**
     * 批量删除
     *
     * @param list
     */
    public void batchDeleteMaterial(List<Map<String, Object>> list) {
        String sql = "DELETE FROM p_exploration_material_sum WHERE sum_id=?";
        getJdbcTemplate().batchUpdate(sql, list, 100, new ParameterizedPreparedStatementSetter<Map<String, Object>>() {
            @Override
            public void setValues(PreparedStatement ps, Map<String, Object> map) throws SQLException {
                ps.setLong(1, Long.parseLong(String.valueOf(map.get("sum_id"))));
            }
        });
    }

    /**
     * 踏勘典设导入模块
     *
     * @param module_id 模块id
     */
    @Override
    public void impExpModule(Long module_id, Long exploration_id) {
        String sql = "insert into p_exploration_material (material_number, erp_id, erp_code, exploration_id, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id,sort) " +
                "select material_number, erp_id, erp_code, ?, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, 1, module_material_id,sort" +
                " from c_module_material where module_id=? and parent_id is null";
        getJdbcTemplate().update(sql, new Object[]{exploration_id, module_id});
    }

    /**
     * 获取踏勘点典设材料
     * objType 0 材料 ，1 模块
     *
     * @param id
     * @param exploration_id
     * @return
     */
    @Override
    public Map<String, Object> getExpM(int objType, Long id, Long exploration_id) {
        String m = "";
        String mid = "";
        if (objType == 0) {
            m = "c_material";
            mid = "material_id";
        } else if (objType == 1) {
            m = "c_module_material";
            mid = "module_material_id";
        } else {
            return null;
        }
        StringBuffer sql = new StringBuffer();
        sql.append("select IFNULL(b.num,0) n,a.* from p_exploration_material a ");
        sql.append("LEFT JOIN ");
        sql.append(m);
        sql.append(" b on a.obj_id = b.");
        sql.append(mid);
        sql.append(" where a.obj_type = ? and ");
        if (objType == 0) {
            sql.append(" a.obj_id=? and a.exploration_id =? ");
        } else if (objType == 1) {
            sql.append(" a.obj_id in (select module_material_id from c_module_material where module_id=?) and a.exploration_id =? ");
        }


        return findOneObject(sql.toString(), objType, id, exploration_id);
    }

    /**
     * 更新踏勘典设数量
     *
     * @param num
     * @param objType
     * @param material_id
     * @param exploration_id
     */
    @Override
    public void upExpMNum(float num, int objType, Long material_id, Long exploration_id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" update p_exploration_material a set num = num + ? ");
        sql.append(" where a.obj_type = ? and ");
        if (objType == 0) {
            sql.append(" a.obj_id=? and a.exploration_id =?;");
        } else if (objType == 1) {
            sql.append(" a.obj_id in (select module_material_id from c_module_material where module_id=?) and a.exploration_id =?;");
        }


        getJdbcTemplate().update(sql.toString(), num, objType, material_id, exploration_id);
    }

    /**
     * 变更踏勘点的材料
     *
     * @param param
     */
    @Override
    public void upCodesetMaterial(Map<String, String> param) {
        Object matId = param.get("matId");//踏勘点位材料id
        String sql_mat = "select obj_type,exploration_id from p_exploration_material where material_id=?";
        Map<String, Object> result = findOneObject(sql_mat, new Object[]{matId});
        Object obj_type = result.get("obj_type");//踏勘点材料类型
        Long exploration_id = Long.valueOf(result.get("exploration_id").toString());
        Long obj_id = Long.valueOf(param.get("obj_id"));//更新后模块后者材料的id

        Object num = param.get("num");//数量
        Object use_type = param.get("use_type");//11利旧 12新建

        StringBuilder sb = new StringBuilder();
        if (obj_id == 0) {
            sb.append(" update p_exploration_material set num = ?,use_type =?");
            sb.append(" where material_id = ?");
            getJdbcTemplate().update(sb.toString(),new Object[]{num,use_type,matId});
        } else {
            //删除已经存在的踏勘点材料
            String sql_del = "delete from p_exploration_material where material_id=?";
            getJdbcTemplate().update(sql_del, new Object[]{matId});
            //添加选择则的材料或者模块（+数量和是否利旧标识+）
            if (obj_type == false) {
                sb.append(" INSERT INTO p_exploration_material");
                sb.append(" (material_number,erp_id,erp_code,exploration_id,NAME,big_category,middle_category,");
                sb.append(" small_category,spec,unit,unit_weight,estimate_price,original,thumb,height,length,");
                sb.append(" num,consume_steel,consume_concrete,remark,is_wire,wire_id,obj_type,obj_id,use_type");
                sb.append(" ) SELECT");
                sb.append(" material_number,erp_id,erp_code,?, NAME,big_category,middle_category,");
                sb.append(" small_category,spec,unit,unit_weight,estimate_price,original,thumb,height,length,");
                sb.append(" ?,consume_steel,consume_concrete,remark,is_wire,wire_id,0,material_id,?");
                sb.append(" from c_material where material_id = ?");
            } else {
                sb.append(" insert into p_exploration_material");
                sb.append(" (material_number, erp_id, erp_code, exploration_id, name, big_category, middle_category,");
                sb.append(" small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length,");
                sb.append("  num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type, obj_id,use_type)");
                sb.append(" select");
                sb.append(" material_number, erp_id, erp_code, ?, name, big_category, middle_category,");
                sb.append("  small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length,");
                sb.append(" ?, consume_steel, consume_concrete, remark, is_wire, wire_id, 1, module_material_id,?");
                sb.append(" from c_module_material where module_id=?");
            }
            getJdbcTemplate().update(sb.toString(), new Object[]{exploration_id, num, use_type, obj_id});
        }
        //踏勘材料汇总
        this.updateExpCodeset(exploration_id);
    }

    /**
     * 得到module
     *
     * @param module_id
     * @return
     */
    @Override
    public String getReModule(Long module_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select  CONCAT(name,'(',spec,')') n");
        sql.append(" from c_module_material");
        sql.append(" where module_id=? and parent_id is null");
        return getJdbcTemplate().queryForObject(sql.toString(), String.class, module_id);
    }


    /**
     * 踏勘典设导入材料
     *
     * @param material_id    材料id
     * @param exploration_id 踏勘id
     */
    @Override
    public void impExpMaterial(Long material_id, Long exploration_id) {
        String sql = "insert into p_exploration_material (material_number, erp_id, erp_code, exploration_id, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id, obj_type,obj_id,sort) " +
                " select material_number, erp_id, erp_code, ?, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, 1, consume_steel, consume_concrete, remark, is_wire, wire_id, 0,?" +
                " ,sort from c_material where material_id=?";
        getJdbcTemplate().update(sql, new Object[]{exploration_id, material_id,material_id});
    }

    /**
     * 得到material
     *
     * @param material_id
     * @return
     */
    @Override
    public String getReMaerial(Long material_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select  CONCAT(name,'(',spec,')') n");
        sql.append(" from c_material");
        sql.append(" where material_id=? ");
        return getJdbcTemplate().queryForObject(sql.toString(), String.class, material_id);
    }

    /**
     * 修改踏勘典设材料数量
     *
     * @param id       模块id或者材料id
     * @param num
     * @param use_type
     */
    @Override
    public Long upExpMaterial(Long id, Integer num, Integer use_type) {
        String sql = "update p_exploration_material set num=?,use_type=? where material_id=?";
        getJdbcTemplate().update(sql, new Object[]{num, use_type, id});
        String sql2 = "select exploration_id from p_exploration_material where material_id=?";
        Map<String, Object> result = findOneObject(sql2, new Object[]{id});
        if (result != null) {
            return Long.parseLong(String.valueOf(result.get("exploration_id")));
        }
        return null;
    }

    /**
     * 删除踏勘典设材料
     *
     * @param id 模块id或者材料id
     */
    @Override
    public Long delExpMaterial(Long id) {
        String sql2 = "select exploration_id from p_exploration_material where material_id=?";
        Map<String, Object> result = findOneObject(sql2, new Object[]{id});
        String sql = "delete from p_exploration_material where material_id=?";
        getJdbcTemplate().update(sql, new Object[]{id});
        if (result != null) {
            return Long.parseLong(String.valueOf(result.get("exploration_id")));
        }
        return null;
    }

    /**
     * 获取保存的踏勘典设列表
     *
     * @param exploration_id
     * @return
     */
    @Override
    public List<Map<String, Object>> queryExpCodeset(Long exploration_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select  material_id id,`name`,spec,unit,num,obj_type,use_type ");
        sql.append("from p_exploration_material ");
        sql.append("where exploration_id = ? order by sort, material_id asc");
        return getJdbcTemplate().query(sql.toString(), new Object[]{exploration_id}, getColumnMapRowMapper());
    }

    /**
     * 汇总材料批量插入
     *
     * @param param 传入参数
     */
    public void batchInsertMaterial(final List<Map<String, Object>> param, final Long exploration_id) {
        String sql = "insert into p_exploration_material_sum (material_number, erp_id, erp_code, exploration_id, name, big_category, middle_category, small_category, spec, unit, unit_weight, estimate_price, original, thumb, height, length, num, consume_steel, consume_concrete, remark, is_wire, wire_id) " +
                " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        getJdbcTemplate().batchUpdate(sql, param, 100, new ParameterizedPreparedStatementSetter<Map<String, Object>>() {
            @Override
            public void setValues(PreparedStatement ps, Map<String, Object> map) throws SQLException {
                ps.setString(1, String.valueOf(map.get("material_number")));
                ps.setString(2, String.valueOf(map.get("erp_id")));
                ps.setString(3, String.valueOf(map.get("erp_code")));
                ps.setLong(4, exploration_id);
                ps.setString(5, String.valueOf(map.get("name")));
                if (map.get("big_category") == null) {
                    ps.setNull(6, Types.INTEGER);
                } else {
                    ps.setInt(6, Integer.parseInt(String.valueOf(map.get("big_category"))));
                }
                if (map.get("middle_category") == null) {
                    ps.setNull(7, Types.INTEGER);
                } else {
                    ps.setInt(7, Integer.parseInt(String.valueOf(map.get("middle_category"))));
                }
                if (map.get("small_category") == null) {
                    ps.setNull(8, Types.INTEGER);
                } else {
                    ps.setInt(8, Integer.parseInt(String.valueOf(map.get("small_category"))));
                }
                ps.setString(9, String.valueOf(map.get("spec")));
                ps.setString(10, String.valueOf(map.get("unit")));
                if (map.get("unit_weight") == null) {
                    ps.setNull(11, Types.FLOAT);
                } else {
                    ps.setFloat(11, Float.parseFloat(String.valueOf(map.get("unit_weight"))));
                }
                if (StringUtils.isEmpty(map.get("estimate_price"))) {
                    ps.setNull(12, Types.DECIMAL);
                } else {
                    ps.setBigDecimal(12, new BigDecimal(String.valueOf(map.get("estimate_price"))));
                }
                ps.setString(13, String.valueOf(map.get("original")));
                ps.setString(14, String.valueOf(map.get("thumb")));
                if (StringUtils.isEmpty(map.get("height"))) {
                    ps.setNull(15, java.sql.Types.FLOAT);
                } else {
                    ps.setFloat(15, Float.parseFloat(String.valueOf(map.get("height"))));
                }
                if (StringUtils.isEmpty(map.get("length"))) {
                    ps.setNull(16, java.sql.Types.FLOAT);
                } else {
                    ps.setFloat(16, Float.parseFloat(String.valueOf(map.get("length"))));
                }
                ps.setFloat(17, Float.parseFloat(String.valueOf(map.get("num"))));
                if (StringUtils.isEmpty(map.get("consume_steel"))) {
                    ps.setNull(18, java.sql.Types.FLOAT);
                } else {
                    ps.setFloat(18, Float.parseFloat(String.valueOf(map.get("consume_steel"))));
                }
                if (StringUtils.isEmpty(map.get("consume_concrete"))) {
                    ps.setNull(19, java.sql.Types.FLOAT);
                } else {
                    ps.setFloat(19, Float.parseFloat(String.valueOf(map.get("consume_concrete"))));
                }
                if (StringUtils.isEmpty(map.get("remark"))) {
                    ps.setNull(20, Types.VARCHAR);
                } else {
                    ps.setString(20, String.valueOf(map.get("remark")));
                }
                ps.setBoolean(21, Boolean.parseBoolean(String.valueOf(map.get("is_wire"))));
                if (StringUtils.isEmpty(map.get("wire_id"))) {
                    ps.setNull(22, java.sql.Types.FLOAT);
                } else {
                    ps.setLong(22, Long.parseLong(String.valueOf(map.get("wire_id"))));
                }
            }
        });
    }

    /**
     * 汇总材料批量修改
     *
     * @param param 传入参数
     */
    public void batchUpdateMaterial(final List<Map<String, Object>> param) {
        String sql = "update p_exploration_material_sum set num=? where sum_id=?";
        getJdbcTemplate().batchUpdate(sql, param, 100, new ParameterizedPreparedStatementSetter<Map<String, Object>>() {
            @Override
            public void setValues(PreparedStatement ps, Map<String, Object> map) throws SQLException {
                ps.setFloat(1, Float.parseFloat(String.valueOf(map.get("num"))));
                ps.setLong(2, Long.parseLong(String.valueOf(map.get("sum_id"))));
            }
        });
    }

    /**
     * 初始化插入和更新数据
     *
     * @param listAll    操作所有材料
     * @param listSum    原始材料
     * @param insertList 插入数据
     * @param updateList 修改数据
     */
    public void initObjects(List<Map<String, Object>> listAll, List<Map<String, Object>> listSum,
                            List<Map<String, Object>> insertList, List<Map<String, Object>> updateList,
                            List<Map<String, Object>> deleteList) {
        if (listSum.size() > 0) {
            //初始化插入和更新数据，并且标注listSum中不删除的数据
            for (int i = 0; i < listAll.size(); i++) {
                Map<String, Object> temp = listAll.get(i);
                Map<String, Object> temp2 = isExistSum(temp, listSum);
                if (temp2 != null) {
                    temp.put("sum_id", temp2.get("sum_id"));
                    updateList.add(temp);
                } else {
                    insertList.add(temp);
                }
            }
            //初始化要删除的参数
            for (int i = 0; i < listSum.size(); i++) {
                Map<String, Object> temp3 = listSum.get(i);
                if (StringUtils.isEmpty(temp3.get("deleted"))) {
                    deleteList.add(temp3);
                }
            }
        } else {
            insertList.addAll(listAll);
        }
    }

    /**
     * 典设材料是否在汇总材料中
     *
     * @param temp
     * @param listSum
     * @return
     */
    public Map<String, Object> isExistSum(Map<String, Object> temp, List<Map<String, Object>> listSum) {
        String material_number1 = String.valueOf(temp.get("material_number"));
        for (int i = 0; i < listSum.size(); i++) {
            Map<String, Object> temp2 = listSum.get(i);
            String material_number2 = String.valueOf(temp2.get("material_number"));
            if (material_number1.equals(material_number2)) {
                temp2.put("deleted", 0);
                return temp2;
            }
        }
        return null;
    }

    /**
     * 获取典设材料
     *
     * @param exploration_id 踏勘id
     * @return
     */
    public List<Map<String, Object>> getPexplorationMaterial(Long exploration_id) {
        String sql = "select * from p_exploration_material where exploration_id=? and obj_type=0 and use_type = 12";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql, new Object[]{exploration_id});
        return list;
    }

    /**
     * 获取典设模块材料
     *
     * @param exploration_id 踏勘id
     * @return
     */
    public List<Map<String, Object>> getCmoduleMaterial(Long exploration_id) {
        String sql = "select cmm.*,cmm.num*pem.num as countNum from c_module_material cmm " +
                " left join p_exploration_material pem on cmm.parent_id=pem.obj_id" +
                " where pem.exploration_id=? and obj_type=1 and cmm.parent_id is not NULL  and pem.use_type=12";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql, new Object[]{exploration_id});
        return list;
    }

    /**
     * 排除2个List中重复的相同数量相加
     *
     * @param list1 踏勘典设材料
     * @param list2 踏勘典设模块材料
     */
    public List<Map<String, Object>> list1CopyList2(List<Map<String, Object>> list1, List<Map<String, Object>> list2) {
        if (list1.size() > 0) {
            if (list2.size() > 0) {
                for (int i = 0; i < list2.size(); i++) {
                    Map<String, Object> temp = list2.get(i);
                    //判断 temp是否存在list1
                    Map<String, Object> temp1 = isExsitList1(temp, list1);
                    temp.put("num",String.valueOf(temp.get("countNum")));
                    if (temp1 != null) {//存在
                        float num2 = Float.parseFloat(String.valueOf(temp.get("num")));
                        float num1 = Float.parseFloat(String.valueOf(temp1.get("num")));
                        temp1.put("num", num1+ num2);
                    } else {//否者加入list1中
                        list1.add(temp);
                    }
                }
                return list1;
            } else {
                return list1;
            }
        } else {
            if (list2.size() > 0) {
                for (int i = 0; i < list2.size(); i++) {
                    Map<String, Object> temp = list2.get(i);
                     temp.put("num",String.valueOf(temp.get("countNum")));

                }
                return list2;
            }
            else
            {
                return list2;
            }


        }
    }

    /**
     * 获取汇总表材料
     *
     * @param exploration_id 踏勘id
     * @return
     */
    public List<Map<String, Object>> getPexplorationMaterialSum(Long exploration_id) {
        String sql = "select * from p_exploration_material_sum where exploration_id=?";
        List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql, new Object[]{exploration_id});
        return list;
    }

    /**
     * map1是否存在list1中
     *
     * @param map1  模块中的材料
     * @param list1 典设中的材料
     * @return
     */
    public Map<String, Object> isExsitList1(Map<String, Object> map1, List<Map<String, Object>> list1) {
        boolean boo = false;
        String material_number = String.valueOf(map1.get("material_number"));
        for (int i = 0; i < list1.size(); i++) {
            Map<String, Object> map2 = list1.get(i);
            String material_number2 = String.valueOf(map2.get("material_number"));
            if (material_number.equals(material_number2)) {
                return map2;
            }
        }
        return null;
    }
}
